with base_table as (--基表,为后面单独计算包和单使用
    select waybill_no
         , package_code
         , scan_site_id
         , scan_site_code
         , scan_site
         , site_agent_code
         , site_agent_name
         , scan_time
         , network_type
         , scan_user_code
         , scan_user
         , pistol_id
         , source
         , nvl(weight, 0.00)                                as weight
--          , case when waybill_no like 'B%' then waybill_no else package_code end as package_code_1 --新字段,将包号都打出来
         , case when package_code is null then 0 else 1 end as is_package --0代表无所属包号
         , case
               when waybill_no like 'B%' then 1
               when waybill_no like 'JT%' then 2
               else 0 end                                   as way_bag    --1代表包,2代表JT运单
         , substr(scan_time, 1, 10)                         as date_time
    from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
    where dt = date_add('{{ execution_date | cst_ds }}', -1)
      and waybill_no is not null
),
     centerarrival_base as (--中心到件  -运单
         select *
         from (
                  select *
                       , row_number()
                          over (partition by waybill_no,scan_site_code order by weight desc ,is_package desc) as rn
                  from base_table
--                   where waybill_no like 'JT%'
                  where way_bag = 2 --运单
              ) b
         where rn = 1
     ),
     waybill_incre as (--运单表
         select waybill_no
              , pick_network_id
              , pick_network_code
              , pick_network_name
              , dispatch_network_code
              , dispatch_network_name
              , nvl(package_inside_charge_weight, 0.00) as package_inside_charge_weight --内部计费重量
              , dt
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -15)
           and waybill_no is not null
     ),
     bagging_base as (
         select waybill_no     --包号 包号对应的建包网点
              , scan_site_id   --建包网点
              , scan_site_code --建包网点
         from (
                  select waybill_no     --运单号
                       , scan_time      --建包扫描时间
                       , scan_site_id   --建包网点
                       , scan_site_code --建包网点
                       , row_number() over (partition by waybill_no order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_bagging_base_dt
                  where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -15) and date_add('{{ execution_date | cst_ds }}', 1)
                    and waybill_no like 'B%'
              ) a
         where a.rn = 1
     ),
     bagging_waybill_base as ( --运单对应建包信息   新增逻辑 运单对应的建包网点
         select waybill_no     --运单号
              , scan_site_id   --建包网点
              , scan_site_code --建包网点
         from (
                  select waybill_no     --运单号
                       , scan_time      --建包扫描时间
                       , scan_site_id   --建包网点
                       , scan_site_code --建包网点
                       , row_number() over (partition by waybill_no order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_bagging_base_dt
                  where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -15) and date_add('{{ execution_date | cst_ds }}', 1)
                    and waybill_no not like 'B%'
                    and waybill_no is not null
              ) a
         where a.rn = 1
     ),
     network_whole as (--网点维表
         select id
              , code
              , name
              , agent_code
              , agent_name
              , network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
         from jms_dim.dim_network_whole_massage
     ),
     express_base as (--剔除问题件   --增加问题件中17a的类型
         select waybill_no
         from jms_dwd.dwd_wide_abnormal_detail_waybill_dt
         where dt between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 1)
           and (is_reback = 1 or (is_difficult = 1 and difficult_type_code = '17a')) --增加问题件中17a的类型
           and waybill_no is not null
         group by waybill_no
     ),
     --寄件网点及其取件集散和中心的取件关系
     t_pcik_relation_filter as (
         select a.network_id as site_id
              , code         as compare_code
         from jms_dim.dim_lmdm_sys_network_distributi a
         where a.distribution_type in (2, 3)
         group by network_id
                , code
     ),
     --派件网点及其派件集散和中心的派件关系
     t_dispatch_relation_filter as (
         select a.network_id as site_id
              , code         as compare_code
         from jms_dim.dim_lmdm_sys_network_distributi a
         where a.distribution_type in (1, 4)
         group by network_id
                , code
     ),
     unsign_wide as (--剔除中心前5天,有与派件网点存在交货关系的
         select t1.waybill_no
              , t1.scan_site_code
              , t1.scan_time
         from (
                  select a.waybill_no
                       , a.scan_site_code
                       , a.scan_time
                       , b.pick_network_id
                  from (
                           select waybill_no
                                , scan_site_code
                                , min(scan_time) as scan_time
                           from jms_dwd.dwd_tab_barscan_centerarrival_base_dt a
                           where dt >= date_add('{{ execution_date | cst_ds }}', -6)
                             and dt <= date_add('{{ execution_date | cst_ds }}', -1)
                             and waybill_no is not null
                             and waybill_no like 'JT%'
                             and a.weight is not null
                             and a.weight <> 0
                           group by waybill_no
                                  , scan_site_code
                       ) a
                           join
                       (
                           select waybill_no
                                , pick_network_id
                           from waybill_incre
                       ) b
                       on a.waybill_no = b.waybill_no
              ) t1
                  join
              (
                  select a.network_id as site_id
                       , code         as compare_code
                  from jms_dim.dim_lmdm_sys_network_distributi a
                  where a.distribution_type = 2
                  group by network_id
                         , code
              ) t2
              on t1.pick_network_id = t2.site_id
                  and t1.scan_site_code = t2.compare_code
     ),
     pack_5_trace as (--剔除中心前5天,有与派件网点存在交货关系的
         select waybill_no
              , scan_site_code
              , scan_time
         from (
                  select a.waybill_no
                       , a.scan_site_code
                       , a.scan_time
                       , a.weight
                       , b.scan_site_id   as pack_scan_site_id
                       , b.scan_site_code as pack_scan_site_code
                  from (
                           select waybill_no
                                , scan_site_code
                                , max(nvl(weight, 0.00)) as weight
                                , min(scan_time)         as scan_time
                           from jms_dwd.dwd_tab_barscan_centerarrival_base_dt a
                           where dt >= date_add('{{ execution_date | cst_ds }}', -6)
                             and dt <= date_add('{{ execution_date | cst_ds }}', -1)
                             and waybill_no like 'B%'
                             and waybill_no is not null
                             and a.weight is not null
                             and a.weight <> 0
                           group by waybill_no
                                  , scan_site_code
                       ) a
                           join
                       (
                           select waybill_no     --包号 包号对应的建包网点
                                , scan_site_id   --建包网点
                                , scan_site_code --建包网点
                           from bagging_base
                       ) b
                       on a.waybill_no = b.waybill_no
              ) t1
                  join
              (
                  select a.network_id as site_id
                       , code         as compare_code
                  from jms_dim.dim_lmdm_sys_network_distributi a
                  where a.distribution_type = 2
                  group by network_id
                         , code
              ) t2
              on t1.pack_scan_site_id = t2.site_id
                  and t1.scan_site_code = compare_code
     ),
     -------------------------------------应称重单号明细-------------------------------------
     master_date_1 as (--主数据
         select cent.waybill_no                  --运单
              , cent.package_code                --包号
              , cent.scan_site_id                --中心id
              , cent.scan_site_code              --中心code
              , cent.scan_site                   --中心名
              , cent.site_agent_code             --代理区code
              , cent.site_agent_name             --代理区名
              , cent.scan_time                   --扫描时间
              , cent.scan_user_code              --扫描人code
              , cent.scan_user                   --扫描名
              , cent.pistol_id                   --机器
              , cent.source                      --数据源
              , cent.weight                      --重量
              , cent.date_time
              , way.pick_network_code            --寄件网点
              , way.pick_network_name            --寄件网点名
              , way.dispatch_network_code
              , way.package_inside_charge_weight --内部计费重量
         from centerarrival_base cent
                  left join waybill_incre way
                            on cent.waybill_no = way.waybill_no
     ),
     master_date as (
         select aa.waybill_no                            --运单
              , aa.package_code                          --包号
              , aa.scan_site_id                          --中心id
              , aa.scan_site_code                        --中心code
              , aa.scan_site                             --中心名
              , aa.site_agent_code                       --代理区code
              , aa.site_agent_name                       --代理区名
              , aa.scan_time                             --扫描时间
              , aa.scan_user_code                        --扫描人code
              , aa.scan_user                             --扫描名
              , aa.pistol_id                             --机器
              , aa.source                                --数据源
              , aa.weight                                --重量
              , aa.date_time
              , netwho.id         as pick_network_id     --寄件网点id
              , aa.pick_network_code                     --寄件网点
              , aa.pick_network_name                     --寄件网点名
              , netwho.agent_code as pick_agent_code     --寄件网点代理区code
              , netwho.agent_name as pick_agent_name     --寄件网点代理区
              , netwho_2.id       as dispatch_network_id --派件网点id
              , aa.package_inside_charge_weight          --内部计费重量
         from master_date_1 aa
                  left join network_whole netwho
                            on aa.pick_network_code = netwho.code
                  left join network_whole netwho_2
                            on aa.dispatch_network_code = netwho_2.code
     ),
     master_date_2 as (
         select waybill_no                                                 --运单
              , package_code                                               --包号
              , scan_site_code                                             --中心code
              , scan_site                                                  --中心名
              , site_agent_code                                            --代理区code
              , site_agent_name                                            --代理区名
              , scan_time                                                  --扫描时间
              , scan_user_code                                             --扫描人code
              , scan_user                                                  --扫描名
              , pistol_id                                                  --机器
              , source                                                     --数据源
              , weight                                                     --重量
              , date_time
              , pick_network_id                                            --寄件网点id
              , pick_network_code                                          --寄件网点
              , pick_network_name                                          --寄件网点名
              , pick_agent_code                                            --寄件网点代理区code
              , pick_agent_name                                            --寄件网点代理区
              , dispatch_network_id                                        --派件网点id
              , package_inside_charge_weight                               --内部计费重量
--               , pcik.compare_code                       as jjwd_rela       --寄件网点和中心具有取件关系 null为不存在关系
--               , dispa.compare_code                      as tcj_rela        --剔除同城件 null代表同城件
              , if(pcik.compare_code is not null, 1, 0) as jjrel_flag      --1代表寄件网点和中心存在交货关系
              , if(dispa.compare_code is null, 1, 0)    as pjrel_flag      --1代表派件网点和中心不存在派货关系
              , if(dispa.compare_code is not null and site_agent_code <> pick_agent_code, 1,
                   0)                                   as pick_agent_flag --目的网点与本中心存在交取关系 且寄件网点和本中心不属于同一代理区的数据
         from master_date mas
                  left join t_pcik_relation_filter pcik
                            on mas.pick_network_id = pcik.site_id
                                and mas.scan_site_code = pcik.compare_code --寄件网点和中心具有取件关系
                  left join t_dispatch_relation_filter dispa
                            on mas.dispatch_network_id = dispa.site_id
                                and mas.scan_site_code = dispa.compare_code --剔除同城件

     ),
     need_billcode_detail as (
         select a.waybill_no                   as billcode            --运单
              , a.package_code                 as package_code        --所属包号
              , a.scan_site_code               as site_code           --中心code
              , a.scan_site                    as site_name           --中心名
              , a.site_agent_code              as proxy_area_code     --代理区code
              , a.site_agent_name              as proxy_area_name     --代理区名
              , a.scan_time                    as scan_time           --扫描时间
              , a.scan_user_code               as scan_user_code      --扫描人code
              , a.scan_user                    as scan_user           --扫描名
              , a.pistol_id                    as pistol_id           --机器
              , a.source                       as source              --数据源
              , a.weight                       as weight              --重量
              , a.pick_network_id              as pick_network_id     --寄件网点id
              , a.pick_network_code            as pick_network_code   --寄件网点
              , a.pick_network_name            as pick_network_name   --寄件网点名
              , a.dispatch_network_id          as dispatch_network_id --派件网点id
              , ''                             as bagging_site_id     --建包网点
              , ''                             as bagging_site_code   --建包网点
              , ''                             as package_qty         --包内件
              , a.package_inside_charge_weight as package_weight      --包内件内部计费重量
              , 1                              as flag
              , a.date_time                    as date_time
              , jjrel_flag                                            --1代表寄件网点和中心存在交货关系
              , pjrel_flag                                            --1代表派件网点和中心不存在派货关系
--               , if(a.jjwd_rela is not null, 1, 0) as jjrel_flag          --1代表寄件网点和中心存在交货关系
--               , if(a.tcj_rela is null, 1, 0)      as pjrel_flag          --1代表派件网点和中心不存在派货关系
              , a.date_time                    as dt
         from master_date_2 a
                  left anti
                  join express_base b
                       on a.waybill_no = b.waybill_no
                  left anti
                  join unsign_wide c
                       on a.waybill_no = c.waybill_no
                           and a.scan_time > c.scan_time
                  left anti
                  join bagging_waybill_base d --新增剔除逻辑 查询建包扫描表，在本中心有建包记录的剔除
                       on a.waybill_no = d.waybill_no
                           and a.scan_site_code = d.scan_site_code
         where jjrel_flag = 1
           and a.package_code is null
           and pick_agent_flag <> 1 --目的网点与本中心存在交取关系 且寄件网点和本中心不属于同一代理区的数据 剔除
--          and a.jjwd_rela is not null
--             and a.waybill_no not like 'B%'
     ),
     -------------------------------------应称重单号明细-------------------------------------


     -------------------------------------应称重包号明细-------------------------------------
     package_dist as (--去重包号,拿到所有包号
         select *
         from (
                  select waybill_no                                                                           as billcode        --包号
                       , package_code                                                                         as package_code    --所属包号
                       , scan_site_code                                                                       as site_code       --中心code
                       , scan_site                                                                            as site_name       --中心名
                       , site_agent_code                                                                      as proxy_area_code --代理区code
                       , site_agent_name                                                                      as proxy_area_name --代理区名
                       , scan_time                                                                            as scan_time       --扫描时间
                       , scan_user_code                                                                       as scan_user_code  --扫描人code
                       , scan_user                                                                            as scan_user       --扫描名
                       , pistol_id                                                                            as pistol_id       --机器
                       , source                                                                               as source          --数据源
                       , weight                                                                               as weight          --重量
                       , row_number()
                          over (partition by waybill_no, scan_site_code order by weight desc, scan_time asc ) as rn
                  from base_table
--                   where waybill_no like 'B%'
                  where way_bag = 1 --包
              ) a
         where a.rn = 1
     ),
     package_dist_bagging as (--拿到建包网点
         select dis.billcode        --包号
              , dis.package_code    --所属包号
              , dis.site_code       --中心code
              , dis.site_name       --中心名
              , dis.proxy_area_code --代理区code
              , dis.proxy_area_name --代理区名
              , dis.scan_time       --扫描时间
              , dis.scan_user_code  --扫描人code
              , dis.scan_user       --扫描名
              , dis.pistol_id       --机器
              , dis.source          --数据源
              , dis.weight          --重量
              , bag.scan_site_id    --建包网点
              , bag.scan_site_code  --建包网点
         from package_dist dis
                  left join bagging_base bag
                            on dis.billcode = bag.waybill_no --包号
     ),
     way_pag_net as (--运单,包号,站点去重
         select waybill_no
              , package_code
              , scan_site_code
         from base_table bt
         where (package_code is not null
             and scan_site_code is not null)
           and substr(waybill_no, 1, 1) <> 'B'
         group by waybill_no
                , package_code
                , scan_site_code
     ),
     master_pack_way as (--拿到包对应的所有运单,只要包和单在站点有过关系的,都算包内件.存在一个运单多次扫描,有所属包号和无所属包号的数据
         select bagg.billcode        --包号
              , bagg.package_code    --所属包号
              , bagg.site_code       --中心code
              , bagg.site_name       --中心名
              , bagg.proxy_area_code --代理区code
              , bagg.proxy_area_name --代理区名
              , bagg.scan_time       --扫描时间
              , bagg.scan_user_code  --扫描人code
              , bagg.scan_user       --扫描名
              , bagg.pistol_id       --机器
              , bagg.source          --数据源
              , bagg.weight          --重量
              , bagg.scan_site_id    --建包网点
              , bagg.scan_site_code  --建包网点
              , wayb.waybill_no      --运单号
         from package_dist_bagging bagg
                  left join way_pag_net wayb
                            on bagg.billcode = wayb.package_code
                                and bagg.site_code = wayb.scan_site_code
     ),
     whole_master_pack_way_1 as (
         select ma.billcode        --包号
              , ma.package_code    --所属包号
              , ma.site_code       --中心code
              , ma.site_name       --中心名
              , ma.proxy_area_code --代理区code
              , ma.proxy_area_name --代理区名
              , ma.scan_time       --扫描时间
              , ma.scan_user_code  --扫描人code
              , ma.scan_user       --扫描名
              , ma.pistol_id       --机器
              , ma.source          --数据源
              , ma.weight          --重量
              , ma.scan_site_id    --建包网点
              , ma.scan_site_code  --建包网点
              , ma.waybill_no      --运单号
              , wa.pick_network_code
              , wa.pick_network_name
              , wa.dispatch_network_code
              , wa.dispatch_network_name
              , wa.package_inside_charge_weight
         from master_pack_way ma
                  left join waybill_incre wa
                            on ma.waybill_no = wa.waybill_no
     ),
     whole_master_pack_way as (
         select aa.billcode        --包号
              , aa.package_code    --所属包号
              , aa.site_code       --中心code
              , aa.site_name       --中心名
              , aa.proxy_area_code --代理区code
              , aa.proxy_area_name --代理区名
              , aa.scan_time       --扫描时间
              , aa.scan_user_code  --扫描人code
              , aa.scan_user       --扫描名
              , aa.pistol_id       --机器
              , aa.source          --数据源
              , aa.weight          --重量
              , aa.scan_site_id    --建包网点
              , aa.scan_site_code  --建包网点
              , aa.waybill_no      --运单号
              , n1.id as pick_network_id
              , aa.pick_network_code
              , aa.pick_network_name
              , n2.id as dispatch_network_id
              , aa.dispatch_network_code
              , aa.dispatch_network_name
              , aa.package_inside_charge_weight
         from whole_master_pack_way_1 aa
                  left join network_whole n1
                            on aa.pick_network_code = n1.code
                  left join network_whole n2
                            on aa.dispatch_network_code = n2.code
     ),
     relation_filter as (
         select wh.billcode                                    --包号
              , wh.package_code                                --所属包号
              , wh.site_code                                   --中心code
              , wh.site_name                                   --中心名
              , wh.proxy_area_code                             --代理区code
              , wh.proxy_area_name                             --代理区名
              , wh.scan_time                                   --扫描时间
              , wh.scan_user_code                              --扫描人code
              , wh.scan_user                                   --扫描名
              , wh.pistol_id                                   --机器
              , wh.source                                      --数据源
              , wh.weight                                      --重量
              , wh.scan_site_id                                --建包网点
              , wh.scan_site_code                              --建包网点
              , wh.waybill_no                                  --运单号
              , wh.pick_network_id                             --寄件网点
              , wh.pick_network_code                           --寄件网点
              , wh.pick_network_name                           --寄件网点
              , wh.dispatch_network_id                         --派件网点
              , wh.dispatch_network_code                       --派件网点
              , wh.dispatch_network_name                       --派件网点
              , wh.package_inside_charge_weight                --内部计费重量
              , pr.site_id                       as pr_site_id --交货网点
              , if(pr.site_id is null, 1, 0)     as jjrel_flag --1代表包内件不存在交货关系
              , dr.site_id                       as dr_site_id --派货网点
              , if(dr.site_id is not null, 1, 0) as pjrel_flag --1代表包内件存在派活关系
         from whole_master_pack_way wh
                  left join t_pcik_relation_filter pr
                            on wh.pick_network_id = pr.site_id
                                and wh.site_code = pr.compare_code
                  left join t_dispatch_relation_filter dr
                            on wh.dispatch_network_id = dr.site_id
                                and wh.site_code = dr.compare_code
     ),
     need_package_detail as (
         select billcode          --包号
              , package_code      --所属包号
              , site_code         --中心code
              , site_name         --中心名
              , proxy_area_code   --代理区code
              , proxy_area_name   --代理区名
              , a.scan_time       --扫描时间
              , scan_user_code    --扫描人code
              , scan_user         --扫描名
              , pistol_id         --机器
              , source            --数据源
              , weight            --重量
              , pick_network_id
              , pick_network_code
              , pick_network_name
              , dispatch_network_id
              , bagging_site_id   --建包网点
              , bagging_site_code --建包网点
              , package_qty
              , package_weight
              , flag
              , date_time
              , jjrel_flag        --1代表包内件存在交货关系
              , pjrel_flag        --1代表包内件存在派活关系
              , dt
         from (
                  select billcode                                     as billcode          --包号
                       , package_code                                 as package_code      --所属包号
                       , site_code                                    as site_code         --中心code
                       , site_name                                    as site_name         --中心名
                       , proxy_area_code                              as proxy_area_code   --代理区code
                       , proxy_area_name                              as proxy_area_name   --代理区名
                       , scan_time                                    as scan_time         --扫描时间
                       , scan_user_code                               as scan_user_code    --扫描人code
                       , scan_user                                    as scan_user         --扫描名
                       , pistol_id                                    as pistol_id         --机器
                       , source                                       as source            --数据源
                       , weight                                       as weight            --重量
                       , ''                                           as pick_network_id
                       , ''                                           as pick_network_code
                       , ''                                           as pick_network_name
                       , ''                                           as dispatch_network_id
                       , max(scan_site_id)                            as bagging_site_id   --建包网点
                       , max(scan_site_code)                          as bagging_site_code --建包网点
                       , count(waybill_no)                            as package_qty
                       , sum(nvl(package_inside_charge_weight, 0.00)) as package_weight
                       , 2                                            as flag
                       , substr(scan_time, 1, 10)                     as date_time
                       , max(jjrel_flag)                              as jjrel_flag        --1代表包内件存在交货关系
                       , max(pjrel_flag)                              as pjrel_flag        --1代表包内件存在派活关系
                       , substr(scan_time, 1, 10)                     as dt
                  from relation_filter
                  group by billcode        --包号
                         , package_code    --所属包号
                         , site_code       --中心code
                         , site_name       --中心名
                         , proxy_area_code --代理区code
                         , proxy_area_name --代理区名
                         , scan_time       --扫描时间
                         , scan_user_code  --扫描人code
                         , scan_user       --扫描名
                         , pistol_id       --机器
                         , source          --数据源
                         , weight --重量
              ) a
                  join t_pcik_relation_filter br
                       on a.bagging_site_id = br.site_id
                           and a.site_code = br.compare_code
                  left anti
                  join pack_5_trace c
                       on a.billcode = c.waybill_no
                           and a.scan_time > c.scan_time

         -------------------------------------应称重包号明细-------------------------------------
     )
insert
overwrite
table
jms_dm.dm_tab_center_weight_rate_way_detail_needno_dt
partition
(
dt
)
select *
from need_billcode_detail
where dt = date_add('{{ execution_date | cst_ds }}', -1)
union all
select *
from need_package_detail
where dt = date_add('{{ execution_date | cst_ds }}', -1)
 distribute by pmod(hash(rand()), 20);